﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class GuideArrangeCtrl
    {
        #region 获取导游安排数据

        /// <summary>
        /// 检测导游是否存在已经安排的信息
        /// </summary>
        /// <param name="ciceroniId"></param>
        /// <returns></returns>
        public static bool CheckExistGuideArrangeInfo(string ciceroniId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT CiceroniID FROM cic_GuideArrange WHERE CiceroniID='{0}'", ciceroniId.Trim()));

            object obj = db.ExecuteScalar(dbCommand);

            return obj != null;
        }
        /// <summary>
        /// 获取指定团所安排的导游标识和导游姓名
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public static DataTable GetCiceroniIDAndNameFromGuideArrange(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT [CiceroniID], [CiceroniName] FROM cic_GuideArrange WHERE TourgroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取指定团的导游安排数据信息
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public static DataTable GetGuideArrangeData(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //string sql = string.Format(@"SELECT g.*,c.Phone FROM cic_GuideArrange g,bas_Ciceroni c WHERE TourgroupID='{0}'AND g.CiceroniID = c.CiceroniID", tourgroupId);
            //string sql = string.Format(@"SELECT g.*,c.Phone,p.ProviderName FROM cic_GuideArrange g,bas_Ciceroni c,bas_Provider p WHERE TourgroupID='{0}'AND g.CiceroniID = c.CiceroniID and g.OwnerProviderID=p.ProviderID", tourgroupId);
            string sql = string.Format(@"SELECT g.*,p.ProviderName,c.Phone FROM cic_GuideArrange AS g LEFT OUTER JOIN dbo.bas_Ciceroni AS c ON g.CiceroniID = c.CiceroniID LEFT OUTER JOIN dbo.bas_Provider AS p ON c.OwnerProviderID = p.ProviderID
                                         WHERE TourgroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 获取指定团的导游信息
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        public static DataTable GetGuideInfoForPrint(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT cic_GuideArrange.StartDate,bas_Ciceroni.CiceroniName,bas_Ciceroni.Phone,bas_Ciceroni.CiceroniID FROM cic_GuideArrange,bas_Ciceroni WHERE TourgroupID='{0}' and cic_GuideArrange.CiceroniID = bas_Ciceroni.CiceroniID and bas_Ciceroni.OwnerProviderID = 0", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetGuideArrangeData(DateTime _StartDate, DateTime _EndDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //为什么这么复杂的条件，看不明白啊
            string sql = string.Format(@"SELECT [GuideArrangeID], [TourgroupID], [StartDate],[Calendars], [EndDate], [CiceroniID], [CiceroniName],
                                                [Notified], [IsAccompany],[HasEvent], [Remark]  FROM cic_GuideArrange 
                                                 WHERE  ((datediff(minute,StartDate,'{0}')>=0  
                                                 AND datediff(minute,EndDate,'{0}')<=0) 
                                                 OR (datediff(minute,StartDate,'{1}')>=0  
                                                 AND datediff(minute,EndDate,'{1}')<=0) 
                                                 OR (datediff(minute,StartDate,'{0}')<=0  
                                                 AND datediff(minute,EndDate,'{1}')>=0))
                                                 AND OwnerProviderID = 0",
                                                 _StartDate.ToString("yyyy-MM-dd HH:mm"),
                                                 _EndDate.ToString("yyyy-MM-dd HH:mm"));
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DateTime start,end;
                //循环修改时间段信息，用于返回后着色GridView的导游安排Cell
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    start = DateTime.Parse(DateTime.Parse(dr["StartDate"].ToString()).ToString("yyyy-MM-dd"));
                    end = DateTime.Parse(DateTime.Parse(dr["EndDate"].ToString()).ToString("yyyy-MM-dd"));

                    if (_StartDate == end)
                    {
                        dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
                        dr["EndDate"] = _StartDate.ToString("yyyy-MM-dd");
                    }
                    if (_StartDate > start && _EndDate>end)
                    {
                        dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
                        dr["EndDate"] = end.ToString("yyyy-MM-dd");
                    }
                    if (_StartDate < start && _EndDate > end)
                    {
                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
                        dr["EndDate"] = end.ToString("yyyy-MM-dd");
                    }
                    if (_StartDate < start && _EndDate < end)
                    {
                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
                        dr["EndDate"] = _EndDate.ToString("yyyy-MM-dd");
                    }
                    if (start == _EndDate)
                    {
                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
                        dr["EndDate"] = start.ToString("yyyy-MM-dd");
                    }
                    if (_StartDate > start && _EndDate < end)
                    {
                        dr["StartDate"] = _StartDate.ToString("yyyy-MM-dd");
                        dr["EndDate"] = _EndDate.ToString("yyyy-MM-dd");
                    }
                    if (_StartDate == start && _EndDate == end)
                    {
                        dr["StartDate"] = start.ToString("yyyy-MM-dd");
                        dr["EndDate"] = end.ToString("yyyy-MM-dd");
                    }
                }
                return ds.Tables[0];
            }
            else
                return null;
        }        
        /// <summary>
        /// 获取指定年和月的导游安排信息
        /// </summary>
        /// <param name="yearMonth">年和月的DateTime类型数据</param>
        /// <returns></returns>
        public static DataTable GetGuideArrangeData(DateTime yearMonth)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT [GuideArrangeID], [TourgroupID], [StartDate],[Calendars], [EndDate], [CiceroniID], [CiceroniName],
                                                [Notified],[IsAccompany], [HasEvent], [Remark] 
                                                FROM cic_GuideArrange 
                                                WHERE (datepart(year,[StartDate])={0} AND datepart(month,[StartDate])={1}) OR (datepart(year,[EndDate])={2} AND datepart(month,[EndDate])={3})", yearMonth.Year, yearMonth.Month, yearMonth.Year, yearMonth.Month);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据给定的上下团时间取得未安排的导游
        /// </summary>
        /// <param name="startDateTime"></param>
        /// <param name="endDateTime"></param>
        /// <returns></returns>
        public static DataTable GetGuideArrangeData(string cityname,string language,int statusIndex,DateTime startDateTime,DateTime endDateTime)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "";
            switch (statusIndex)
            {
                case 0://待安排
                    sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{2}%' AND CiceroniLanguage like '%{3}%' AND ciceroniid NOT IN 
                                         (SELECT DISTINCT ciceroniid  FROM cic_GuideArrange 
                                         WHERE  (datediff(minute,StartDate,'{0}')>=0  
                                         AND datediff(minute,EndDate,'{0}')<=0) 
                                         OR (datediff(minute,StartDate,'{1}')>=0  
                                         AND datediff(minute,EndDate,'{1}')<=0) 
                                         OR (datediff(minute,StartDate,'{0}')<=0  
                                         AND datediff(minute,EndDate,'{1}')>=0)) ORDER BY CiceroniLevel Desc",
                                         startDateTime.ToString("yyyy-MM-dd HH:mm"),
                                         endDateTime.ToString("yyyy-MM-dd HH:mm"),
                                         cityname,
                                         language);
                    break;
                case 1://已安排
                    sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{2}%' AND CiceroniLanguage like '%{3}%' AND ciceroniid IN 
                                         (SELECT DISTINCT ciceroniid  FROM cic_GuideArrange 
                                         WHERE  (datediff(minute,StartDate,'{0}')>=0  
                                         AND datediff(minute,EndDate,'{0}')<=0) 
                                         OR (datediff(minute,StartDate,'{1}')>=0  
                                         AND datediff(minute,EndDate,'{1}')<=0) 
                                         OR (datediff(minute,StartDate,'{0}')<=0  
                                         AND datediff(minute,EndDate,'{1}')>=0))  ORDER BY CiceroniLevel Desc",
                                         startDateTime.ToString("yyyy-MM-dd HH:mm"),
                                         endDateTime.ToString("yyyy-MM-dd HH:mm"),
                                         cityname,
                                         language);
                    break;
                case 2://全部
                    sql = string.Format(@"SELECT CiceroniID,CiceroniName,Sex,CiceroniLanguage,City FROM bas_Ciceroni WHERE city like '%{0}%' AND CiceroniLanguage like '%{1}%'  ORDER BY CiceroniLevel Desc", cityname, language);
                    break;
                default:
                    break;
            }
            
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据指定日期和导游ID获取导游安排表的日历Calendars内容和主键GuideArrangeID值
        /// </summary>
        /// <param name="yMd"></param>
        /// <param name="ciceroniId">导游ID</param>
        /// <returns></returns>
        public static DataTable GetGuideArrangeData(string yMd, string ciceroniId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT [GuideArrangeID],[Calendars] FROM cic_GuideArrange WHERE convert(varchar(10),[StartDate],121)<='{0}' AND '{1}'<=convert(varchar(10),[EndDate],121) AND  CiceroniID={2}", yMd, yMd, ciceroniId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        #endregion

        #region 导游安排数据的增、删、改
        
        /// <summary>
        /// 增加导游安排
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static int CreateGuideArrangeData(List<GuideArrangeEntity> entities)//GuideArrangeEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder sql = new StringBuilder();
            foreach (var entity in entities)
            {
                sql.AppendFormat(@"INSERT INTO cic_GuideArrange ([TourgroupID], [StartDate],[Calendars],[EndDate], [CiceroniID], [CiceroniName], [Notified], 
                                                                       [HasEvent], [Remark],[IsAccompany],[OwnerProviderID])
                                                                       VALUES('{0}','{1}','{2}','{3}',{4},'{5}', {6},{7},'{8}',{9},0);",
                                                                        entity.TourgroupID,
                                                                        entity.StartDate,
                                                                        entity.Calendars,
                                                                        entity.EndDate,
                                                                        entity.CiceroniID,
                                                                        entity.CiceroniName,
                                                                        entity.Notified == true ? 1 : 0,
                                                                        entity.HasEvent,
                                                                        entity.Remark,
                                                                        entity.IsAccompany == true ? 1 : 0);
            }
            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }
        /// <summary>
        /// 根据导游安排ID，更新日历数据
        /// </summary>
        /// <param name="xmlDocResult">日历数据xml内容</param>
        /// <param name="guideArrangeId">主键id</param>
        /// <returns></returns>
        internal static int UpdateCalendarsData(string xmlDocResult,string guideArrangeId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE cic_GuideArrange SET [Calendars]='{0}' WHERE GuideArrangeID={1}", xmlDocResult, guideArrangeId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 删除指定导游安排编号的数据，同时更新团HasGuide
        /// </summary>
        /// <param name="guideArrangeId"></param>
        internal static void DeleteGuideArrangeData(string guideArrangeId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            
            //1、根据导游安排ID,获取团ID
            //object tourgroupId = db.ExecuteScalar(CommandType.Text,string.Format("SELECT tourgroupid FROM cic_guidearrange WHERE guidearrangeId={0}",guideArrangeId));

            //2、删除
            string sql = string.Format(@"DELETE FROM cic_GuideArrange WHERE GuideArrangeID={0}", guideArrangeId);
            db.ExecuteNonQuery(CommandType.Text, sql);

            //3、判断导游安排表中是否还有1中所获取团号已安排导游，没有则将团表中HasGuid置为false
            //sql = string.Format(@"SELECT COUNT(*) FROM cic_guidearrange WHERE tourgroupid='{0}'", tourgroupId);
            //if (db.ExecuteScalar(CommandType.Text, sql).ToString() == "0")
            //    TeamGroupCtrl.UpdateTourGroupHasGuide(tourgroupId.ToString(),false);
        }

        /// <summary>
        /// 删除指定团导游，用于团取消和删除
        /// </summary>
        /// <param name="tourgroupID"></param>
        internal static void DeleteGuideArrangebyTourgroupID(string tourgroupID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM cic_GuideArrange WHERE TourgroupID='{0}'", tourgroupID);
            db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        /// <summary>
        /// 更新导游安排数据中的‘是否通知’和说明信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static int UpdateGuideArrangeData(GuideArrangeEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @" UPDATE [cic_GuideArrange]
                            SET [IsAccompany]=@IsAccompany, [Remark]=@Remark 
                            WHERE GuidearrangeID=@GuidearrangeID";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "GuidearrangeID", DbType.Int32, entity.GuideArrangeID);
            db.AddInParameter(dbCommand, "IsAccompany", DbType.Int32, entity.IsAccompany == true ? 1 : 0);
            db.AddInParameter(dbCommand, "Remark", DbType.String, entity.Remark);

            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 更新导游安排通知
        /// </summary>
        /// <param name="guideArrangeId"></param>
        /// <param name="notified"></param>
        internal static int UpdateGuideArrangeData(int guideArrangeId, int notified)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @" UPDATE [cic_GuideArrange] SET [Notified]=@Notified WHERE GuidearrangeID=@GuidearrangeID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "GuidearrangeID", DbType.Int32, guideArrangeId);
            db.AddInParameter(dbCommand, "Notified", DbType.Int32, notified);

            return db.ExecuteNonQuery(dbCommand);
        }
    }
}
